Field
Embodiments of the present invention generally relate to database applications. More specifically, embodiments of the present invention relate to reclamation and reuse of primary key values.
Description of Related Art
There are types of database applications in which an event causes a large number of records to be inserted and deleted over a short period of time, while the net increase in the number of records is relatively small and negligible.
For example, an Online Transaction Processing (OLTP) application may provide a means for a user to submit an arbitrary number of input data to a program with a request to analyze the submitted data and update and store results data based on the analysis. Any previous results data must be discarded (deleted), and new results data must be preserved (inserted). In this example over a period of 5 seconds, 5000 records are inserted, and 5000 records are deleted, and the net increase is (0). If this is a multi-user application and users are submitting data every 60 seconds, then on average the application is inserting and deleting 83 records per second.
It is common practice to store records in tables that have an integer based primary key column that has a unique constraint enabled, are indexed, and automatically provide unique values on insert using an auto increment algorithm; that is, for each insert, the database system assigns a value that is (1) plus the value assigned on the previous insert.
The auto increment algorithm advances the values by (1) indefinitely. Database designs that utilize auto increment must anticipate the maximum number of inserts that will occur over the life of the database, and size the capacity of the primary key column to avoid overflow.
In the example above, if the primary column uses a 4-byte integer, its capacity is 2,147,483,647. At 83 inserts per second, the auto increment algorithm would overflow in approximately 298 days. The lifespan of most database applications is measured in years so a 4-byte integer does not provide sufficient capacity. Using an 8-byte integer, the capacity is 9,223,372,036,854,775,807 and overflow would occur in approximately 3,509,654,504 years.
It is common to use 8-byte integer primary keys to avoid the problem of overflow, but its use adversely impacts the performance of any database application and consumes more RAM of the database computer.